﻿/******************************************************************************
'Description:装试工时看板
'Copyright (c) : 通力凯顿（北京）系统集成有限公司
'Writer:zhunan
'create Date:2019.07.10
 *****************************************************************************/
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
namespace ReportBusiness
{
    public class uMESZSWorkTimeInfoLookBoardBusiness
    {
        private uMESReportOracleDAL dal = new uMESReportOracleDAL();
        public DataTable GetFactoryInfo(string factoryid)
        {
            StringBuilder sb = new StringBuilder();
            sb.AppendFormat("SELECT f.factoryid,f.factoryname FROM factory f WHERE f.factoryid ='{0}'", factoryid);
            DataTable dt = dal.GetDataTable(sb.ToString());
            return dt;
        }

        public DataSet GetZSWorkTimeInfoLookBoardData(Dictionary<string,object> para)
        {
            #region 查询批产工时
            string sql = @"
                            SELECT ttt.workcentername,ttt.createMonth,SUM(ttt.employeeWorktime) employeeWorktime
                            FROM
                            (   
                                SELECT w.workcentername,--extract(month from nvl(cki.createdate,wt.createdate)) createMonth,
                                      CASE WHEN(wt.stepinfoid IS NOT NULL AND csw.checktype ='1' ) 
                                               THEN coalesce(wts.SPLITDURATION,wt.totalduration,round(SI.DURATIONPERUNIT / NVL(wt.SPLITCOUNT, 1), 2), 0)
                                           WHEN(wt.stepinfoid IS NOT NULL AND csw.checktype ='2') 
                                               THEN coalesce(wts.SPLITDURATION,wt.totalduration, round(SI.Durationperunit2 / NVL(wt.SPLITCOUNT, 1), 2), 0) 
                                           ELSE coalesce(wt.totalduration, wt.durationperunit, wts.splitduration, round(sd.DURATIONPERUNIT / NVL(wt.SPLITCOUNT, 1), 2), 0) 
                                      END employeeWorktime,
                                      CASE WHEN TRUNC(nvl(cki.createdate,wt.createdate)) >= TRUNC(SYSDATE,'yyyy') - 11 
                                                AND TRUNC(nvl(cki.createdate,wt.createdate)) <= TRUNC(to_date(extract(year from sysdate) || '/1/20','yyyy/mm/dd')) THEN 1
                                           WHEN TRUNC(nvl(cki.createdate,wt.createdate)) >= TRUNC(to_date(extract(year from sysdate) || '/1/21','yyyy/mm/dd'))
                                                AND TRUNC(nvl(cki.createdate,wt.createdate)) <= TRUNC(to_date(extract(year from sysdate) || '/2/20','yyyy/mm/dd')) THEN 2
                                           WHEN TRUNC(nvl(cki.createdate,wt.createdate)) >= TRUNC(to_date(extract(year from sysdate) || '/2/21','yyyy/mm/dd'))
                                                AND TRUNC(nvl(cki.createdate,wt.createdate)) <= TRUNC(to_date(extract(year from sysdate) || '/3/20','yyyy/mm/dd')) THEN 3
                                           WHEN TRUNC(nvl(cki.createdate,wt.createdate)) >= TRUNC(to_date(extract(year from sysdate) || '/3/21','yyyy/mm/dd'))
                                                AND TRUNC(nvl(cki.createdate,wt.createdate)) <= TRUNC(to_date(extract(year from sysdate) || '/4/20','yyyy/mm/dd')) THEN 4
                                           WHEN TRUNC(nvl(cki.createdate,wt.createdate)) >= TRUNC(to_date(extract(year from sysdate) || '/4/21','yyyy/mm/dd'))
                                                AND TRUNC(nvl(cki.createdate,wt.createdate)) <= TRUNC(to_date(extract(year from sysdate) || '/5/20','yyyy/mm/dd')) THEN 5
                                           WHEN TRUNC(nvl(cki.createdate,wt.createdate)) >= TRUNC(to_date(extract(year from sysdate) || '/5/21','yyyy/mm/dd'))
                                                AND TRUNC(nvl(cki.createdate,wt.createdate)) <= TRUNC(to_date(extract(year from sysdate) || '/6/20','yyyy/mm/dd')) THEN 6
                                           WHEN TRUNC(nvl(cki.createdate,wt.createdate)) >= TRUNC(to_date(extract(year from sysdate) || '/6/21','yyyy/mm/dd'))
                                                AND TRUNC(nvl(cki.createdate,wt.createdate)) <= TRUNC(to_date(extract(year from sysdate) || '/7/20','yyyy/mm/dd')) THEN 7
                                           WHEN TRUNC(nvl(cki.createdate,wt.createdate)) >= TRUNC(to_date(extract(year from sysdate) || '/7/21','yyyy/mm/dd'))
                                                AND TRUNC(nvl(cki.createdate,wt.createdate)) <= TRUNC(to_date(extract(year from sysdate) || '/8/20','yyyy/mm/dd')) THEN 8
                                           WHEN TRUNC(nvl(cki.createdate,wt.createdate)) >= TRUNC(to_date(extract(year from sysdate) || '/8/21','yyyy/mm/dd'))
                                                AND TRUNC(nvl(cki.createdate,wt.createdate)) <= TRUNC(to_date(extract(year from sysdate) || '/9/20','yyyy/mm/dd')) THEN 9
                                           WHEN TRUNC(nvl(cki.createdate,wt.createdate)) >= TRUNC(to_date(extract(year from sysdate) || '/9/21','yyyy/mm/dd'))
                                                AND TRUNC(nvl(cki.createdate,wt.createdate)) <= TRUNC(to_date(extract(year from sysdate) || '/10/20','yyyy/mm/dd')) THEN 10
                                           WHEN TRUNC(nvl(cki.createdate,wt.createdate)) >= TRUNC(to_date(extract(year from sysdate) || '/10/21','yyyy/mm/dd'))
                                                AND TRUNC(nvl(cki.createdate,wt.createdate)) <= TRUNC(to_date(extract(year from sysdate) || '/11/20','yyyy/mm/dd')) THEN 11
                                           WHEN TRUNC(nvl(cki.createdate,wt.createdate)) >= TRUNC(to_date(extract(year from sysdate) || '/11/21','yyyy/mm/dd'))
                                                AND TRUNC(nvl(cki.createdate,wt.createdate)) <= TRUNC(to_date(extract(year from sysdate) || '/12/20','yyyy/mm/dd')) THEN 12
                                        END createMonth
                                FROM containersteptimesheet wt
                                LEFT JOIN container c ON wt.containerid = c.containerid
                                LEFT JOIN workcenter w ON w.workcenterid = wt.workcenterid
                                LEFT JOIN stepinfo SI ON si.Stepinfoid=wt.stepinfoid
                                LEFT JOIN stepschedulingdetail sd ON sd.stepid = wt.workflowstepid
                                LEFT JOIN containercheckinfo cki ON cki.containercheckinfoid = wt.containercheckinfoid                    
                                LEFT JOIN containersteptimesheetsplit wts ON wts.containersteptimesheetid = wt.containersteptimesheetid
                                LEFT JOIN containerstepworkreport csw ON cki.containerstepworkreportid=csw.containerstepworkreportid
                                LEFT JOIN userattribute ua ON ua.parentid=c.mfgorderid 
                                LEFT JOIN enginepartplan ep ON NVL(EP.enginecontainerid, EP.containerid)=c.containerid
                                WHERE wt.workerid IS NOT NULL AND c.status='1' AND c.parentcontainerid IS NULL
                                      AND ua.userattributename='发动机'
                                      AND EP.workflowstepid is not null 
                                      AND ((csw.isuse = 0) OR INSTR(si.Description,'集件') > 0 OR INSTR(si.Description,'去封')>0 ) ";
            if (para.ContainsKey("factoryid"))
            {
                sql += string.Format(" AND wt.factoryid = '{0}' ",para["factoryid"]);
            }

            if (para.ContainsKey("startDate"))
            {
                sql += string.Format(" AND NVL(cki.createdate,wt.createdate) >= to_date('{0}','yyyy-mm-dd hh24:mi:ss') ",para["startDate"]);
            }

            if (para.ContainsKey("endDate"))
            {
                sql += string.Format(" AND NVL(cki.createdate,wt.createdate) <= to_date('{0}','yyyy-mm-dd hh24:mi:ss') ", para["endDate"]);
            }

            sql += @"
                            ) ttt
                            GROUP BY ttt.workcentername,ttt.createMonth
                            ORDER BY ttt.workcentername,ttt.createMonth";

            DataTable dtPiChan = dal.GetDataTable(sql);
            #endregion

            #region 查询施工单工时
            sql = @"
                        SELECT ttt.workcentername,ttt.createMonth,SUM(ttt.employeeWorktime) employeeWorktime
                        FROM
                        (
                        SELECT w.workcentername,--extract(month from nvl(cki.createdate,wt.createdate)) createMonth,
                               CASE WHEN(wt.stepinfoid IS NOT NULL AND csw.checktype ='1' ) 
                                     THEN coalesce(wts.SPLITDURATION,wt.totalduration,round(SI.DURATIONPERUNIT / NVL(wt.SPLITCOUNT, 1), 2), 0)
                                     WHEN(wt.stepinfoid IS NOT NULL AND csw.checktype ='2') 
                                     THEN coalesce(wts.SPLITDURATION,wt.totalduration, round(SI.Durationperunit2 / NVL(wt.SPLITCOUNT, 1), 2), 0) 
                                     ELSE coalesce(wt.totalduration, wt.durationperunit, wts.splitduration, round(sd.DURATIONPERUNIT / NVL(wt.SPLITCOUNT, 1), 2), 0) 
                               END employeeWorktime,
                               CASE WHEN TRUNC(nvl(cki.createdate,wt.createdate)) >= TRUNC(SYSDATE,'yyyy') - 11 
                                         AND TRUNC(nvl(cki.createdate,wt.createdate)) <= TRUNC(to_date(extract(year from sysdate) || '/1/20','yyyy/mm/dd')) THEN 1
                                    WHEN TRUNC(nvl(cki.createdate,wt.createdate)) >= TRUNC(to_date(extract(year from sysdate) || '/1/21','yyyy/mm/dd'))
                                         AND TRUNC(nvl(cki.createdate,wt.createdate)) <= TRUNC(to_date(extract(year from sysdate) || '/2/20','yyyy/mm/dd')) THEN 2
                                    WHEN TRUNC(nvl(cki.createdate,wt.createdate)) >= TRUNC(to_date(extract(year from sysdate) || '/2/21','yyyy/mm/dd'))
                                         AND TRUNC(nvl(cki.createdate,wt.createdate)) <= TRUNC(to_date(extract(year from sysdate) || '/3/20','yyyy/mm/dd')) THEN 3
                                    WHEN TRUNC(nvl(cki.createdate,wt.createdate)) >= TRUNC(to_date(extract(year from sysdate) || '/3/21','yyyy/mm/dd'))
                                         AND TRUNC(nvl(cki.createdate,wt.createdate)) <= TRUNC(to_date(extract(year from sysdate) || '/4/20','yyyy/mm/dd')) THEN 4
                                    WHEN TRUNC(nvl(cki.createdate,wt.createdate)) >= TRUNC(to_date(extract(year from sysdate) || '/4/21','yyyy/mm/dd'))
                                         AND TRUNC(nvl(cki.createdate,wt.createdate)) <= TRUNC(to_date(extract(year from sysdate) || '/5/20','yyyy/mm/dd')) THEN 5
                                    WHEN TRUNC(nvl(cki.createdate,wt.createdate)) >= TRUNC(to_date(extract(year from sysdate) || '/5/21','yyyy/mm/dd'))
                                         AND TRUNC(nvl(cki.createdate,wt.createdate)) <= TRUNC(to_date(extract(year from sysdate) || '/6/20','yyyy/mm/dd')) THEN 6
                                    WHEN TRUNC(nvl(cki.createdate,wt.createdate)) >= TRUNC(to_date(extract(year from sysdate) || '/6/21','yyyy/mm/dd'))
                                         AND TRUNC(nvl(cki.createdate,wt.createdate)) <= TRUNC(to_date(extract(year from sysdate) || '/7/20','yyyy/mm/dd')) THEN 7
                                    WHEN TRUNC(nvl(cki.createdate,wt.createdate)) >= TRUNC(to_date(extract(year from sysdate) || '/7/21','yyyy/mm/dd'))
                                         AND TRUNC(nvl(cki.createdate,wt.createdate)) <= TRUNC(to_date(extract(year from sysdate) || '/8/20','yyyy/mm/dd')) THEN 8
                                    WHEN TRUNC(nvl(cki.createdate,wt.createdate)) >= TRUNC(to_date(extract(year from sysdate) || '/8/21','yyyy/mm/dd'))
                                         AND TRUNC(nvl(cki.createdate,wt.createdate)) <= TRUNC(to_date(extract(year from sysdate) || '/9/20','yyyy/mm/dd')) THEN 9
                                    WHEN TRUNC(nvl(cki.createdate,wt.createdate)) >= TRUNC(to_date(extract(year from sysdate) || '/9/21','yyyy/mm/dd'))
                                         AND TRUNC(nvl(cki.createdate,wt.createdate)) <= TRUNC(to_date(extract(year from sysdate) || '/10/20','yyyy/mm/dd')) THEN 10
                                    WHEN TRUNC(nvl(cki.createdate,wt.createdate)) >= TRUNC(to_date(extract(year from sysdate) || '/10/21','yyyy/mm/dd'))
                                         AND TRUNC(nvl(cki.createdate,wt.createdate)) <= TRUNC(to_date(extract(year from sysdate) || '/11/20','yyyy/mm/dd')) THEN 11
                                    WHEN TRUNC(nvl(cki.createdate,wt.createdate)) >= TRUNC(to_date(extract(year from sysdate) || '/11/21','yyyy/mm/dd'))
                                         AND TRUNC(nvl(cki.createdate,wt.createdate)) <= TRUNC(to_date(extract(year from sysdate) || '/12/20','yyyy/mm/dd')) THEN 12
                               END createMonth
                        FROM containersteptimesheet wt
                        LEFT JOIN container c ON wt.containerid = c.containerid
                        LEFT JOIN workcenter w ON w.workcenterid = wt.workcenterid
                        LEFT JOIN stepinfo SI ON si.Stepinfoid=wt.stepinfoid
                        LEFT JOIN stepinfobase sib ON sib.stepinfobaseid=si.Stepinfobaseid
                        LEFT JOIN stepschedulingdetail sd ON sd.stepid = wt.workflowstepid
                        LEFT JOIN containercheckinfo cki ON cki.containercheckinfoid = wt.containercheckinfoid                    
                        LEFT JOIN containersteptimesheetsplit wts ON wts.containersteptimesheetid = wt.containersteptimesheetid
                        LEFT JOIN containerstepworkreport csw ON cki.containerstepworkreportid=csw.containerstepworkreportid
                        LEFT JOIN userattribute ua ON ua.parentid=c.mfgorderid 
                        LEFT JOIN enginepartplan ep ON NVL(EP.enginecontainerid, EP.containerid)=c.containerid
                        WHERE wt.workerid IS NOT NULL AND c.status='1' AND c.parentcontainerid IS NULL
                             AND  ua.userattributename='发动机' AND EP.workflowstepid IS NULL  
                             AND ((csw.isuse = 0) OR INSTR(si.Description,'集件') > 0 OR INSTR(si.Description,'去封')>0 ) ";

            if (para.ContainsKey("factoryid"))
            {
                sql += string.Format(" AND wt.factoryid = '{0}' ", para["factoryid"]);
            }

            if (para.ContainsKey("startDate"))
            {
                sql += string.Format(" AND NVL(cki.createdate,wt.createdate) >= to_date('{0}','yyyy-mm-dd hh24:mi:ss') ", para["startDate"]);
            }

            if (para.ContainsKey("endDate"))
            {
                sql += string.Format(" AND NVL(cki.createdate,wt.createdate) <= to_date('{0}','yyyy-mm-dd hh24:mi:ss') ", para["endDate"]);
            }

            sql += @"
                            ) ttt
                            GROUP BY ttt.workcentername,ttt.createMonth
                            ORDER BY ttt.workcentername,ttt.createMonth";

            DataTable dtSGD = dal.GetDataTable(sql);
            #endregion

            #region 查询油封工时
            sql = @"
                    SELECT ttt.workcentername,ttt.createMonth,SUM(ttt.employeeWorktime) employeeWorktime
                    FROM
                    (
                      SELECT w.workcentername,el.qty * os.oilsealdurationperunit employeeWorktime,--extract(month from el.createdate) createMonth,
                             CASE WHEN TRUNC(el.createdate) >= TRUNC(SYSDATE,'yyyy') - 11 
                                       AND TRUNC(el.createdate) <= TRUNC(to_date(extract(year from sysdate) || '/1/20','yyyy/mm/dd')) THEN 1
                                  WHEN TRUNC(el.createdate) >= TRUNC(to_date(extract(year from sysdate) || '/1/21','yyyy/mm/dd'))
                                       AND TRUNC(el.createdate) <= TRUNC(to_date(extract(year from sysdate) || '/2/20','yyyy/mm/dd')) THEN 2
                                  WHEN TRUNC(el.createdate) >= TRUNC(to_date(extract(year from sysdate) || '/2/21','yyyy/mm/dd'))
                                       AND TRUNC(el.createdate) <= TRUNC(to_date(extract(year from sysdate) || '/3/20','yyyy/mm/dd')) THEN 3
                                  WHEN TRUNC(el.createdate) >= TRUNC(to_date(extract(year from sysdate) || '/3/21','yyyy/mm/dd'))
                                       AND TRUNC(el.createdate) <= TRUNC(to_date(extract(year from sysdate) || '/4/20','yyyy/mm/dd')) THEN 4
                                  WHEN TRUNC(el.createdate) >= TRUNC(to_date(extract(year from sysdate) || '/4/21','yyyy/mm/dd'))
                                       AND TRUNC(el.createdate) <= TRUNC(to_date(extract(year from sysdate) || '/5/20','yyyy/mm/dd')) THEN 5
                                  WHEN TRUNC(el.createdate) >= TRUNC(to_date(extract(year from sysdate) || '/5/21','yyyy/mm/dd'))
                                       AND TRUNC(el.createdate) <= TRUNC(to_date(extract(year from sysdate) || '/6/20','yyyy/mm/dd')) THEN 6
                                  WHEN TRUNC(el.createdate) >= TRUNC(to_date(extract(year from sysdate) || '/6/21','yyyy/mm/dd'))
                                       AND TRUNC(el.createdate) <= TRUNC(to_date(extract(year from sysdate) || '/7/20','yyyy/mm/dd')) THEN 7
                                  WHEN TRUNC(el.createdate) >= TRUNC(to_date(extract(year from sysdate) || '/7/21','yyyy/mm/dd'))
                                       AND TRUNC(el.createdate) <= TRUNC(to_date(extract(year from sysdate) || '/8/20','yyyy/mm/dd')) THEN 8
                                  WHEN TRUNC(el.createdate) >= TRUNC(to_date(extract(year from sysdate) || '/8/21','yyyy/mm/dd'))
                                       AND TRUNC(el.createdate) <= TRUNC(to_date(extract(year from sysdate) || '/9/20','yyyy/mm/dd')) THEN 9
                                  WHEN TRUNC(el.createdate) >= TRUNC(to_date(extract(year from sysdate) || '/9/21','yyyy/mm/dd'))
                                       AND TRUNC(el.createdate) <= TRUNC(to_date(extract(year from sysdate) || '/10/20','yyyy/mm/dd')) THEN 10
                                  WHEN TRUNC(el.createdate) >= TRUNC(to_date(extract(year from sysdate) || '/10/21','yyyy/mm/dd'))
                                       AND TRUNC(el.createdate) <= TRUNC(to_date(extract(year from sysdate) || '/11/20','yyyy/mm/dd')) THEN 11
                                  WHEN TRUNC(el.createdate) >= TRUNC(to_date(extract(year from sysdate) || '/11/21','yyyy/mm/dd'))
                                       AND TRUNC(el.createdate) <= TRUNC(to_date(extract(year from sysdate) || '/12/20','yyyy/mm/dd')) THEN 12
                             END createMonth
                      FROM enginepartoilseal el
                      LEFT JOIN oilsealcycleinfo os ON os.oilsealcycleinfoid = el.oilsealcycleinfoid
                      LEFT JOIN employee e ON e.employeeid = el.employeeid
                      LEFT JOIN Sessionvalues sv ON sv.employeeid = e.employeeid
                      LEFT JOIN factory f ON f.factoryid = sv.factoryid
                      LEFT JOIN Workcenter w ON w.workcenterid = sv.workcenterid
                      WHERE os.oilsealdurationperunit > 0 AND el.qty > 0 ";
            if (para.ContainsKey("factoryid"))
            {
                sql += string.Format(" AND f.factoryid = '{0}' ", para["factoryid"]);
            }

            if (para.ContainsKey("startDate"))
            {
                sql += string.Format(" AND el.createdate >= to_date('{0}','yyyy-mm-dd hh24:mi:ss') ", para["startDate"]);
            }

            if (para.ContainsKey("endDate"))
            {
                sql += string.Format(" AND el.createdate <= to_date('{0}','yyyy-mm-dd hh24:mi:ss') ", para["endDate"]);
            }

            sql += @"
                     ) ttt
                     GROUP BY ttt.workcentername,ttt.createMonth
                     ORDER BY ttt.workcentername,ttt.createMonth";

            DataTable dtYF = dal.GetDataTable(sql);

            #endregion

            DataSet dsResult = new DataSet();

            //分厂--年工时总额
            DataTable dtTotal = new DataTable();
            dtTotal.Columns.Add("total");//总工时
            dtTotal.Columns.Add("pichan");//批次工时
            dtTotal.Columns.Add("sgd");//施工单工时
            dtTotal.Columns.Add("yf");//油封工时

            DataRow dtTotalRow = dtTotal.NewRow();
            dtTotalRow["pichan"] = dtPiChan.Rows.Count == 0 ? 0 : Convert.ToDecimal(dtPiChan.Compute("Sum(employeeWorktime)", ""));
            dtTotalRow["sgd"] = dtSGD.Rows.Count == 0 ? 0 : Convert.ToDecimal(dtSGD.Compute("Sum(employeeWorktime)", ""));
            dtTotalRow["yf"] = dtYF.Rows.Count == 0 ? 0 : Convert.ToDecimal(dtYF.Compute("Sum(employeeWorktime)", ""));
            dtTotalRow["total"] = Convert.ToDecimal(dtTotalRow["pichan"]) + Convert.ToDecimal(dtTotalRow["sgd"]) + Convert.ToDecimal(dtTotalRow["yf"]);
            dtTotal.Rows.Add(dtTotalRow);

            sql = @"SELECT w.workcentername FROM workcenter w WHERE 1=1 ";

            if (para.ContainsKey("factoryid"))
            {
                sql += string.Format(" AND w.factoryid = '{0}' ", para["factoryid"].ToString());
            }

            DataTable dtWorkcenter = dal.GetDataTable(sql);
            //各工区--年工时总额
            dtWorkcenter.Columns.Add("total");//总工时
            dtWorkcenter.Columns.Add("pichan");//批次工时
            dtWorkcenter.Columns.Add("sgd");//施工单工时
            dtWorkcenter.Columns.Add("yf");//油封工时

            foreach (DataRow dr in dtWorkcenter.Rows)
            {
                string filter = string.Format(" workcentername = '{0}'",dr["workcentername"]);
                dr["pichan"] = dtPiChan.Select(filter).Length > 0 ? Convert.ToDecimal(dtPiChan.Compute("Sum(employeeWorktime)", filter)) : 0;
                dr["sgd"] = dtSGD.Select(filter).Length > 0 ? Convert.ToDecimal(dtSGD.Compute("Sum(employeeWorktime)", filter)) : 0;
                dr["yf"] = dtYF.Select(filter).Length > 0 ? Convert.ToDecimal(dtYF.Compute("Sum(employeeWorktime)", filter)) : 0;
                dr["total"] = Convert.ToDecimal(dr["pichan"]) + Convert.ToDecimal(dr["sgd"]) + Convert.ToDecimal(dr["yf"]);
            }

            DataTable dtEveryMonthWorkTime = new DataTable();
            dtEveryMonthWorkTime.Columns.Add("month");//总工时
            dtEveryMonthWorkTime.Columns.Add("total");//总工时
            dtEveryMonthWorkTime.Columns.Add("pichan");//批次工时
            dtEveryMonthWorkTime.Columns.Add("sgd");//施工单工时
            dtEveryMonthWorkTime.Columns.Add("yf");//油封工时

            for (int i = 1; i <= 12; i++)
            {
                DataRow dr = dtEveryMonthWorkTime.NewRow();
                dr["month"] = i + "月";
                dr["pichan"] = dtPiChan.Select(string.Format("createMonth = '{0}'", i.ToString())).Length > 0 ? 
                    Convert.ToDecimal(dtPiChan.Compute("Sum(employeeWorktime)", string.Format("createMonth = '{0}'", i.ToString()))) : 0;

                dr["sgd"] = dtSGD.Select(string.Format("createMonth = '{0}'", i.ToString())).Length > 0 ?
                    Convert.ToDecimal(dtSGD.Compute("Sum(employeeWorktime)", string.Format("createMonth = '{0}'", i.ToString()))) : 0;

                dr["yf"] = dtYF.Select(string.Format("createMonth = '{0}'", i.ToString())).Length > 0 ?
                    Convert.ToDecimal(dtYF.Compute("Sum(employeeWorktime)", string.Format("createMonth = '{0}'", i.ToString()))) : 0;

                dr["total"] = Convert.ToDecimal(dr["pichan"]) + Convert.ToDecimal(dr["sgd"]) + Convert.ToDecimal(dr["yf"]);

                dtEveryMonthWorkTime.Rows.Add(dr);
            }

            dsResult.Tables.Add(dtTotal);
            dsResult.Tables.Add(dtWorkcenter);
            dsResult.Tables.Add(dtEveryMonthWorkTime);

            return dsResult;
        }
    }
}
